#!/usr/bin/perl

use Cwd;

$baseurl = getcwd;

if ($ARGV[0] eq 'CREATING') {
    $mode = 'CREATING';
} else {
    $mode = 'UPDATING';
}

if ($ARGV[1] =~ /\//) {
    ($baseurl = $ARGV[1]) =~ s/[\/]*$//;
}

$error_outs = $baseurl . '/errors_Orders.txt';
$print_outs = $baseurl . '/prints_Orders.txt';

binmode(STDOUT, ':utf8');

open (STDERR, ">$error_outs");
open (STDOUT, ">$print_outs");

print "baseurl: " . $baseurl . "\n";

# local system variables
$connect = $baseurl . '/connect.pl';
require $connect;

use IO::File;

use Date::Calc qw(Delta_Days);

#NOTE: IN ORDER TO USE THIS SCRIPT, YOU MUST FIRST QUERY A SET OF ALL ORDER RECORDS THAT DO NOT HAVE SERIALS ('s') OR PERIODICALS ('p') IN THE RLOC OR BLOC

#THEN YOU MUST "EXPORT" INFORMATION FROM CATALOG
#for instance:

#ACQ TYPE    LOCATION    FORM    FUND    ODATE   ORD TYPE    RDATE   RLOC    BLOC    STATUS  VENDOR  RECORD #(ORDER) 
#CREATED(ORDER)  UPDATED(ORDER)  RECORD #(BIBLIO)    CAT DATE    CALL #  040|a

# NOTE: the LOCATION is the ORDER LOCATION

#THE RESULTING TEXT FILE NEEDS TO BE LOADED INTO ./orders/

if ($ARGV[0] eq 'CREATING') {
$next_order_record = 100000;
}

&hashes_and_arrays; #SUBROUTINE AT END OF SCRIPT

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_time = "$HOUR:$MIN:$SEC";

$folder_name = 'orders';

$where = $baseurl . '/orders';

while (defined($next_file = <$where/*.out>)) {

print "filename: " . $next_file . "\n";

($batch_textfiles = $next_file) =~ s/orders/orders_textfiles/g;

$batch_loader = IO::File->new("> $batch_textfiles") || die "cannot open $batch_textfiles for writing: $!\n";
flock($batch_loader, 2);

    open(IN, "< $next_file") || die "cannot open $next_file for reading: $!";

        while(<IN>) {

            if($_ !~ /^ACQ TYPE/) {

                @fields = split(/\t/);

                %query_insert = (
                    acq_type => '',
                    oloc => '',
                    form => '',
                    fund => '',
                    order_date => '',
                    order_type => '',
                    received_date => '',
                    rloc => '',
                    bloc => '',
                    order_status => '',
                    vendor => '',
                    order_record => '',
                    record_owner => $institution,
                    order_created => '',
                    order_updated => '',
                    local_control_number => '',
                    universal_identifier => '',
                    cat_date => '',
                    order2receipt_time => '',
                    receipt2catalog_time => '', 
                    order_callnumber => '',
                    order_lc_class_a => '',
                    order_lc_class_ab => '',
                    cat_agency => ''
                );

##START: CREATE DELETED ORDER RECORDS

if ($ARGV[0] eq 'CREATING') {

$this_order_record = substr($fields[11], 1, 6);

        while($next_order_record < $this_order_record) {

            $query_insert{'order_record'} = $institution . 'o' . $next_order_record;
            $query_insert{'order_status'} = 'x';

            @keys = keys %query_insert;
            @values = values %query_insert;

            $mysql_keys = join(", ", @keys);
            $mysql_values = join("\t", @values);
            $mysql_values =~ s/\\\t/\\ \t/g;

            print $batch_loader $mysql_values . "\n";
        
            $next_order_record++;
        }

        $next_order_record++;
}

##END: CREATE DELETED ORDER RECORDS

###START: CREATE ORDER RECORDS

		$query_insert{'acq_type'} = $fields[0];
		$query_insert{'oloc'} = $fields[1];
		$query_insert{'form'} = $fields[2];
		$query_insert{'fund'} = $fields[3];
		$query_insert{'order_date'} = $fields[4];
		$query_insert{'order_type'} = $fields[5];
		$query_insert{'received_date'} = $fields[6];
		$query_insert{'rloc'} = $fields[7];
		$query_insert{'bloc'} = $fields[8];
		$query_insert{'order_status'} = $fields[9];
		$query_insert{'vendor'} = $fields[10];
        $query_insert{'order_record'} = $institution . 'o' . substr($fields[11], 1, 6);
		$query_insert{'order_created'} = $fields[12];
		$query_insert{'order_updated'} = $fields[13];
		$query_insert{'local_control_number'} = $institution . substr($fields[14], 0, 8);
		$query_insert{'cat_date'} = $fields[15];
		$query_insert{'order_callnumber'} = $fields[16];
        $query_insert{'order_lc_class_a'} = substr($fields[16], 0, 1);
            if($fields[16] =~ /^([a-zA-Z]*)/) {
                $query_insert{'order_lc_class_ab'} = $1;
            }
		($query_insert{'cat_agency'} = $fields[17]) =~ s/\n//;

                foreach $date_field( order_date, received_date, order_created, order_updated, cat_date ) {
                    if($query_insert{$date_field} =~ /([0-9]{2})-([0-9]{2})-([0-9]{4})/) {
                        $query_insert{$date_field} = $3 . "-" . $1 . "-" . $2;
                    } elsif($query_insert{$date_field} =~ /([0-9]{2})-([0-9]{2})-([0-9]{2})/) {
                        $query_insert{$date_field} = "19" . $3 . "-" . $1 . "-" . $2;
                    }
                }

        if(($query_insert{'order_date'} !~ / -  - /) && ($query_insert{'received_date'} !~ / -  - /)) {
            $query_insert{'order2receipt_time'} = Delta_Days( substr($query_insert{'order_date'}, 0, 4), substr($query_insert{'order_date'}, 5, 2), substr($query_insert{'order_date'}, 8, 2), substr($query_insert{'received_date'}, 0, 4), substr($query_insert{'received_date'}, 5, 2), substr($query_insert{'received_date'}, 8, 2)) + 1;
        }

        if(($query_insert{'received_date'} !~ / -  - /) && ($query_insert{'cat_date'} !~ / -  - /)) {
            $query_insert{'receipt2catalog_time'} = Delta_Days( substr($query_insert{'received_date'}, 0, 4), substr($query_insert{'received_date'}, 5, 2), substr($query_insert{'received_date'}, 8, 2), substr($query_insert{'cat_date'}, 0, 4), substr($query_insert{'cat_date'}, 5, 2), substr($query_insert{'cat_date'}, 8, 2)) + 1;
        }

###END: CREATE ORDER RECORDS

####START: PRINT VALUES TO BATCH FILES

@keys = keys %query_insert;
@values = values %query_insert;

$mysql_keys = join(", ", @keys);
$mysql_values = join("\t", @values);
$mysql_values =~ s/\\\t/\\ \t/g;

print $batch_loader $mysql_values . "\n";

####END: PRINT VALUES TO BATCH FILES

                undef($mysql_values);
                undef(@values);
                undef(@keys);
                undef(%query_insert);

        }
    
    }

    close(IN) || "cannot close $next_file: $!";

unlink($next_file);

    $batch_loader->autoflush(1);
    close($batch_loader, 2) || "cannot close $batch_loader: $!";

}

$orders_keys_textfiles = $baseurl . '/' . $folder_name . '_textfiles/orders.key';

$orders_keys_loader = IO::File->new("> $orders_keys_textfiles") || die "cannot open $orders_keys_textfiles for writing: $!\n";
binmode( $orders_keys_loader, ':utf8' );
flock($orders_keys_loader, 2);

    print $orders_keys_loader $mysql_keys . "\n";

        $orders_keys_loader->autoflush(1);
        close($orders_keys_loader, 2) || "cannot close $orders_keys_loader: $!";


($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_of_file_creation_time = "$HOUR:$MIN:$SEC";

use DBI;

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { mysql_enable_utf8 =>1 }) or die "couldn't connect to database: " . DBI->errstr;

@current_time = localtime;

$last_update_time = ($current_time[5] + 1900) . "-" . ($current_time[4] + 1) . "-$current_time[3] $current_time[2]:$current_time[1]:$current_time[0]";

print "last_update_time: " . $last_update_time . "\n";

if ($ARGV[0] eq 'CREATING') {
    $sth = $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'orders_record_table'}");
    $this_table = $db_local_tables_hash{'orders_record_table'};
} else {
    $sth = $dbh->do("TRUNCATE TABLE $db_local_tables_hash{'orders_record_temp_table'}");
    $this_table = $db_local_tables_hash{'orders_record_temp_table'};
}

$where = $baseurl . '/' . $folder_name . '_textfiles';

#####START: INITIAL TABLE LOAD

#if ($ARGV[0] eq 'CREATING') {
#    $dbh->do("ALTER TABLE " . $this_table . " DISABLE KEYS");
#}

while (defined($next_file = <$where/*.key>)) {

    open(IN, "< $next_file") || die "cannot open $next_file for reading: $!";

            while(<IN>) {
                $mysql_keys = $_;
                chomp($mysql_keys);
            }
    close(IN) || "cannot close $next_file: $!";

}


while (defined($next_file = <$where/*.out>)) {

    $sth2 = $dbh->do("LOAD DATA INFILE '" . $next_file . "' REPLACE INTO TABLE $this_table FIELDS TERMINATED BY '\t' (" . $mysql_keys . ")") || print "intial load died in local\n";

unlink($next_file);

}

unlink($orders_keys_textfiles);

#if ($ARGV[0] eq 'CREATING') {
#    $dbh->do("ALTER TABLE " . $this_table . " ENABLE KEYS");
#}

#####END: INITIAL TABLE LOAD

######START: UPDATE ACTUAL ORDER TABLE WITH DATA IN TEMP TABLE

if ($ARGV[0] ne 'CREATING') {

$this_table = $db_local_tables_hash{'orders_record_table'};
$that_table = $db_local_tables_hash{'orders_record_temp_table'};

    $querystring = "SHOW FIELDS FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

    while ( @row = $query->fetchrow_array ) {
        push (@keys, $row[0]); ## field name
    }

$query->finish();

pop(@keys);

@update_strings = ();

    foreach $field_name (@keys) {
        push(@update_strings, $this_table . '.' . $field_name . ' = ' . $that_table . '.' . $field_name);
    }

    $temp_updates = join(", ", @update_strings);

        $querystring = "UPDATE $this_table, $that_table
            SET $temp_updates
            WHERE $this_table.order_record = $that_table.order_record
            AND $this_table.local_control_number = $that_table.local_control_number";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();


        $querystring = "DELETE $that_table.*
            FROM $that_table
            LEFT JOIN $this_table
            ON $that_table.order_record = $this_table.order_record
            WHERE $that_table.order_record = $this_table.order_record
            AND $that_table.local_control_number = $this_table.local_control_number";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();

        $querystring = "INSERT $this_table
            ($mysql_keys)
            SELECT $mysql_keys from $that_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or print "couldn't execute statement: $querystring";

        $query->finish();

}

######END: UPDATE ACTUAL ORDER TABLE WITH DATA IN TEMP TABLE

#######START: UPDATE $that_table TABLE WITH universal_identifiers

$this_table = $db_local_tables_hash{'bibs_record_table'};
$that_table = $db_local_tables_hash{'orders_record_table'};

$update_string = "UPDATE $that_table, $this_table
set $that_table.universal_identifier = $this_table.universal_identifier
WHERE $that_table.solr_update >= ?
AND $that_table.local_control_number = $this_table.local_control_number";

print $update_string . "\n" . $last_update_time . "\n";


$query = $dbh->prepare($update_string)
                    or die("couldn't prepare statement: ". $dbh->errstr);

                $query->execute($last_update_time)
                    or die("couldn't execute statement: ". $dbh->errstr);

                $query->finish();

$update_string = "UPDATE $that_table, $this_table
set $that_table.order_status = 'x'
WHERE $this_table.bib_status = 'x' 
AND $that_table.order_status != 'x'
AND $that_table.local_control_number = $this_table.local_control_number";

$query = $dbh->prepare($update_string)
                    or die("couldn't prepare statement: ". $dbh->errstr);

                $query->execute()
                    or die("couldn't execute statement: ". $dbh->errstr);

                $query->finish();


#######END: UPDATE $that_table TABLE WITH universal_identifiers

########START: CREATING FILES FOR LOAD INTO CONSORTIUM ORDERSTABLES

if ($consortium ne '') {

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_external_update_time = "$HOUR:$MIN:$SEC";

foreach $table_name (order) {

$this_table = $db_local_tables_hash{$table_name . 's_record_table'};

$recordcount = 0;

    $querystring = "SHOW FIELDS FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

    while ( @row = $query->fetchrow_array ) {
        push (@fields_array, $row[0]); ## field name
    }

$query->finish();

pop(@fields_array);

$fields_array_string = join(", ", @fields_array);

$consortium_keys_textfiles = $baseurl . '/consortium_' . $table_name . 's_textfiles/consortium' . $table_name . 's.key';

$consortium_keys_loader = IO::File->new("> $consortium_keys_textfiles") || die "cannot open $consortium_keys_textfiles for writing: $!\n";
binmode( $consortium_keys_loader, ':utf8' );
flock($consortium_keys_loader, 2);

    print $consortium_keys_loader $fields_array_string . "\n";

        $consortium_keys_loader->autoflush(1);
        close($consortium_keys_loader, 2) || "cannot close $consortium_keys_loader: $!";

if ($ARGV[0] eq 'CREATING') {
    $IN_string = "'n', 's', 'z', 'x'";
} else {
    $IN_string = "'n', 's', 'z'";
}

$limit_start = 0;
$rownumber = 1;
$status = $table_name . '_status';
while($rownumber > 0) {

        $querystring = "SELECT * from $this_table
            WHERE solr_update >= ?
            AND $status NOT IN ($IN_string)
            LIMIT ?, 10000";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute($last_update_time, $limit_start)
            or die("couldn't execute statement: ". $dbh->errstr);

$limit_start += 10000;
$rownumber = $query->rows();

while(@query_array = $query->fetchrow_array()) {
    $int = $recordcount / 10000;

    if ($int =~ m/^\d+$/) {

        if($recordcount > 0) {
        $consortium_batch_loader->autoflush(1);
        close($consortium_batch_loader, 2) || "cannot close $consortium_batch_loader: $!";
        }

$consortium_batch_textfiles = $baseurl . '/consortium_' . $table_name . 's_textfiles/consortium_from_2' . $folder_name . '_' . $table_name . $recordcount .  '.txt';

$consortium_batch_loader = IO::File->new("> $consortium_batch_textfiles") || die "cannot open $consortium_batch_textfiles for writing: $!\n";
binmode( $consortium_batch_loader, ':utf8' );
flock($consortium_batch_loader, 2);


    }

$recordcount++;

pop(@query_array);

foreach(@query_array) {
    s/\t/\\t/g;
}

    $query_array_string = join("\t", @query_array);
    $query_array_string =~ s/\\\t/\\ \t/g;
    $query_array_string =~ s/\n/\\n/g;

    print $consortium_batch_loader $query_array_string . "\n";
    $line_count++;
    }

}

$query->finish();

if($recordcount > 0) {
        $consortium_batch_loader->autoflush(1);
        close($consortium_batch_loader, 2) || "cannot close $consortium_batch_loader: $!";
}

undef(@fields_array);

}

########END: CREATING FILES FOR LOAD INTO CONSORTIUM ORDERSTABLES

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_external_update_time = "$HOUR:$MIN:$SEC";

}

$dbh->disconnect;

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_time = "$HOUR:$MIN:$SEC";

print "Start time: " . $start_time . "\nEnd of file creation time: " . $end_of_file_creation_time . "\nEnd time: " . $end_time . "\n";

#SUBROUTINES

sub hashes_and_arrays {

$hash_files = $baseurl . '/hashfiles';

while (defined($next_hash_file = <$hash_files/*.pl>)) {
require $next_hash_file;
}

}

